#!/usr/bin/env bash
set -uo pipefail

#==============================================================#
# File      :   pg-default-privilege
# Ctime     :   2020-12-16
# Mtime     :   2021-03-16
# Desc      :   setup default privilege for target role and database
# Path      :   /pg/bin/pg-default-privilege
# Depend    :   psql
# Author    :   Vonng(fengruohang@outlook.com)
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#

PROG_NAME="$(basename $0))"
PROG_DIR="$(cd $(dirname $0) && pwd)"

#==============================================================#
#                           Parameters                         #
#==============================================================#
# system default role names
DBROLE_ADMIN='dbrole_admin'
DBROLE_READONLY='dbrole_readonly'
DBROLE_OFFLINE='dbrole_offline'
DBROLE_READWRITE='dbrole_readwrite'

#==============================================================#
#                             Usage                            #
#==============================================================#
function usage() {
	cat <<-'EOF'
		NAME
			pg-default-privilege   -- setup default privilege for database and user

		SYNOPSIS
			pg-default-privilege <username> [database=template1]

		DESCRIPTION
			object create in target database by target user will have default privileges
			properly set: (dbrole_readonly=read dbrole_readwrite=write dbrole_admin=create)

			template1 is the default target database, postgres is the default admin user
			which means user postgres will have right default privileges in all NEWLY
			created database.

			                      Default access privileges
				Owner     | Schema |   Type   |         Access privileges
			--------------+--------+----------+-----------------------------------
			 username     |        | function | =X/postgres
						  |        |          | postgres=X/postgres
						  |        |          | dbrole_readonly=X/postgres
						  |        |          | dbrole_offline=X/postgres
			 username     |        | schema   | postgres=UC/postgres
						  |        |          | dbrole_readonly=U/postgres
						  |        |          | dbrole_offline=U/postgres
						  |        |          | dbrole_admin=C/postgres
			 username     |        | sequence | postgres=rwU/postgres
						  |        |          | dbrole_readonly=r/postgres
						  |        |          | dbrole_readwrite=wU/postgres
						  |        |          | dbrole_offline=r/postgres
			 username     |        | table    | postgres=arwdDxt/postgres
						  |        |          | dbrole_readonly=r/postgres
						  |        |          | dbrole_readwrite=awd/postgres
						  |        |          | dbrole_offline=r/postgres
						  |        |          | dbrole_admin=Dxt/postgres

		EXAMPLES
			pg-default-privilege  postgres      template1
			pg-default-privilege  dbuser_admin  template1

			pg-default-privilege  postgres      postgres
			pg-default-privilege  dbuser_admin  postgres

	EOF
	exit 1
}


#--------------------------------------------------------------#
# Name: pg_default_privilege
# Desc: object create in target database by target user will have
#       default privileges properly set
# Arg1: user name, postgres by default
# Arg2: database name, template1 by default
#--------------------------------------------------------------#
function pg-default-privilege(){
	local username=${1}
	local database=${2-'template1'}
	echo "ALTER DEFAULT PRIVILEGES FOR ROLE ${username} ON ${database};"
	psql -qAXtw ${database} <<-EOF
		-- readonly access
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT USAGE ON SCHEMAS TO "${DBROLE_READONLY}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT SELECT ON TABLES TO "${DBROLE_READONLY}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT SELECT ON SEQUENCES TO "${DBROLE_READONLY}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT EXECUTE ON FUNCTIONS TO "${DBROLE_READONLY}";
		-- offline access
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT USAGE ON SCHEMAS TO "${DBROLE_OFFLINE}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT SELECT ON TABLES TO "${DBROLE_OFFLINE}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT SELECT ON SEQUENCES TO "${DBROLE_OFFLINE}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT EXECUTE ON FUNCTIONS TO "${DBROLE_OFFLINE}";
		-- write access
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT INSERT, UPDATE, DELETE ON TABLES TO "${DBROLE_READWRITE}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT USAGE, UPDATE ON SEQUENCES TO "${DBROLE_READWRITE}";
		-- admin access
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO "${DBROLE_ADMIN}";
		ALTER DEFAULT PRIVILEGES FOR ROLE ${username} GRANT CREATE ON SCHEMAS TO "${DBROLE_ADMIN}";

	EOF
}

#==============================================================#
#                             Main                             #
#==============================================================#
if (( $# < 1 )); then
	usage
fi
case "$1" in
	-h | --help) usage ;;
esac

pg-default-privilege "$@"